﻿
CREATE FUNCTION dbo.credit_TradeLinesByAmountAndMR
	(
	@ApplID int,
	@CoApplID int = NULL,
	@MR int = 0,
	@Amount int = NULL,
	@Status varchar(64) = NULL,
	@Type varchar(64) = NULL,
	@LastDate smalldatetime = NULL
	)
RETURNS tinyint
AS
BEGIN

DECLARE @HCT TABLE(HighCredit int)
INSERT INTO @HCT
SELECT _HighCreditAmount FROM   v_CreditLiability
WHERE ApplicantID = @ApplID AND
			(BorrowerID = @ApplID OR CoBorrowerID = @CoApplID OR @CoApplID IS NULL) AND
			(_AccountStatusType = @Status OR @Status IS NULL) AND
			(_AccountType = @Type OR @Type IS NULL) AND
			(_LastActivityDate >= @LastDate OR @LastDate IS NULL) AND
			_MonthsReviewedCount >= @MR
ORDER BY _HighCreditAmount DESC

IF @Amount IS NULL BEGIN DECLARE @C int SELECT @C=COUNT(*)FROM @HCT	RETURN @C END


IF(SELECT SUM(HighCredit)FROM(SELECT TOP 1 * FROM @HCT)T) >= @Amount RETURN 1
IF(SELECT SUM(HighCredit)FROM(SELECT TOP 2 * FROM @HCT)T) >= @Amount RETURN 2
IF(SELECT SUM(HighCredit)FROM(SELECT TOP 3 * FROM @HCT)T) >= @Amount RETURN 3
IF(SELECT SUM(HighCredit)FROM(SELECT TOP 4 * FROM @HCT)T) >= @Amount RETURN 4
IF(SELECT SUM(HighCredit)FROM(SELECT TOP 5 * FROM @HCT)T) >= @Amount RETURN 5
IF(SELECT SUM(HighCredit)FROM(SELECT TOP 6 * FROM @HCT)T) >= @Amount RETURN 6
IF(SELECT SUM(HighCredit)FROM(SELECT TOP 7 * FROM @HCT)T) >= @Amount RETURN 7
IF(SELECT SUM(HighCredit)FROM(SELECT TOP 8 * FROM @HCT)T) >= @Amount RETURN 8

RETURN 0
END

